package com.qst.itoffer.dao;

import java.sql.*;

import com.qst.itoffer.bean.ResumeBasicinfo;
import com.qst.itoffer.util.DBUtil;

@SuppressWarnings("ALL")
public class ResumeDao {

    private final String SQL = "ALTER TABLE tb_resume_basicinfo AUTO_INCREMENT = 1";

    /**
     * 简历基本信息添加
     * @param basicinfo
     * @param applicantId
     * @return
     */
    public int add(ResumeBasicinfo basicinfo, int applicantId) {
        int basicinfoID = 0;
        String sql = "INSERT INTO tb_resume_basicinfo("
                + "basicinfo_id,applicant_id,realname, gender, birthday, current_loc, "
                + "resident_loc, telephone, email, job_intension, job_experience, head_shot) "
                + "VALUES(0,?,?,?,?,?,?,?,?,?,?,?)";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pst1 = null;
        PreparedStatement pst2 = null;
        PreparedStatement pst3 = null;
        try {
            pst1 = conn.prepareStatement(sql);
            pst3 = conn.prepareStatement(SQL);
            pst1.setInt(1, applicantId);
            pst1.setString(2, basicinfo.getRealName());
            pst1.setString(3, basicinfo.getGender());

            pst1.setDate(4, new Date(basicinfo.getBirthday().getTime()));

            pst1.setString(5, basicinfo.getCurrentLoc());
            pst1.setString(6, basicinfo.getResidentLoc());
            pst1.setString(7, basicinfo.getTelephone());
            pst1.setString(8, basicinfo.getEmail());
            pst1.setString(9, basicinfo.getJobIntension());
            pst1.setString(10, basicinfo.getJobExperience());
            pst1.setString(11, basicinfo.getHeadShot());
            pst3.executeUpdate();
            pst1.executeUpdate();

            String sql2 = "SELECT BASICINFO_ID FROM TB_RESUME_BASICINFO";

            pst2 = conn.prepareStatement(sql2);
            ResultSet rs = pst2.executeQuery();
            if (rs.next()) {
                basicinfoID = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeJDBC(null, pst1, conn);
            DBUtil.closeJDBC(null, pst2, null);
            DBUtil.closeJDBC(null, pst2, null);
        }
        return basicinfoID;
    }

    /**
     * 简历照片更新
     *
     * @param basicinfoId
     * @param newFileName
     */
    public void updateHeadShot(int basicinfoId, String newFileName) {
        String sql = "UPDATE tb_resume_basicinfo SET head_shot=? WHERE basicinfo_id=?";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1, newFileName);
            pst.setInt(2, basicinfoId);
            pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeJDBC(null, pst, conn);
        }
    }

    /**
     * 根据简历标识查询简历基本信息
     * @param applicantId
     * @return
     */
    public ResumeBasicinfo selectBasicinfoByID(int applicantId) {
        ResumeBasicinfo resume = new ResumeBasicinfo();
        String sql = "SELECT * FROM tb_resume_basicinfo WHERE applicant_id=?";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1, applicantId);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                resume.setBasicinfoId(rs.getInt("basicinfo_id"));
                resume.setRealName(rs.getString("realname"));
                resume.setGender(rs.getString("gender"));

                resume.setBirthday(rs.getDate("birthday"));

                resume.setCurrentLoc(rs.getString("current_loc"));
                resume.setResidentLoc(rs.getString("resident_loc"));
                resume.setTelephone(rs.getString("telephone"));
                resume.setEmail(rs.getString("email"));
                resume.setJobIntension(rs.getString("job_intension"));
                resume.setJobExperience(rs.getString("job_experience"));
                resume.setHeadShot(rs.getString("head_shot"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeJDBC(null, pst, conn);
        }
        return resume;
    }

    /**
     * 更新简历基本信息
     * @param basicinfo
     */
    public void update(ResumeBasicinfo basicinfo) {
        String sql = "UPDATE tb_resume_basicinfo "
                + "SET realname=?, gender=?,birthday=?,current_loc=?,resident_loc=?,"
                + "telephone=?,email=?,job_intension=?,job_experience=? "
                + "WHERE basicinfo_id=?";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1, basicinfo.getRealName());
            pst.setString(2, basicinfo.getGender());
            pst.setTimestamp(3, basicinfo.getBirthday() == null ? null
                    : new Timestamp(basicinfo.getBirthday().getTime()));
            pst.setString(4, basicinfo.getCurrentLoc());
            pst.setString(5, basicinfo.getResidentLoc());
            pst.setString(6, basicinfo.getTelephone());
            pst.setString(7, basicinfo.getEmail());
            pst.setString(8, basicinfo.getJobIntension());
            pst.setString(9, basicinfo.getJobExperience());
            pst.setInt(10, basicinfo.getBasicinfoId());
            pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeJDBC(null, pst, conn);
        }
    }

}
